// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;

namespace SnapObjects.Data
{
    internal class SqlWhereBuilder : SqlWhereAndOr<ISqlWhereAndOr>, ISqlWhereBuilder, ISqlWhereAndOr
    {
        protected ISqlGroupBuilder _group;
        public SqlWhereBuilder(
            ISqlBuilder builder,
            SqlBuildClause where,
            SqlBuildClause group,
            SqlBuildClause having,
            SqlBuildClause order) : base(builder, where)
        {
            _group = new SqlGroupBuilder(builder, group, having, order);
        }

        protected override ISqlWhereAndOr BuilderResult()
        {
            return this;
        }

        private ISqlWhereAndOr AddWhere(WhereElement element)
        {
            if (!_clause.TryGet(out WhereElement where))
            {
                _clause.Add(element);
            }
            else
            {
                throw new InvalidOperationException("Repeatedly calling 'Where'.");
            }

            return this.BuilderResult();
        }

        /// <summary>
        ///     Determines whether any search condition has been added to the WHERE clause of the current 
        ///     object using a Where or WhereRaw method and so on.
        /// </summary>
        public bool HasWhere => _clause.Has<WhereElement>();

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression 
        ///     on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter on the right of the operator.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(string left, ISqlParameter parameter)
        {
            return this.Where(left, SqlBinaryOperator.Equals, parameter);
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression 
        ///     on the left of the operator and a SQL parameter on the right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter on the right of the operator.
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            _clause.AddParameter(parameter);

            return this.AddWhere(
                new WhereElement(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), parameter, SqlRightType.Parameter));
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Specifies SQL expressions on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(string left, string right)
        {
            return this.Where(left, SqlBinaryOperator.Equals, right);
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Specifies SQL expressions on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(string left, SqlBinaryOperator sqlOperator, string right)
        {
            return this.AddWhere(
                new WhereElement(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), right, SqlRightType.Clause));
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on the left 
        ///     of the operator and a SQL subquery on the right. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(string left, ISqlBuilder sqlBuilder)
        {
            return this.Where(left, SqlBinaryOperator.Equals, sqlBuilder);
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on
        ///     the left of the operator and a SQL subquery on the right.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            /*var builder = (SqlBuilderBase)sqlBuilder;

            _clause.AddParameters(builder.Statement.Parameters);

            return this.AddWhere(
                new WhereElement(left, sqlOperator.SqlOperator, builder.Statement, SqlRightType.Statement));*/

            return this.Where(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), sqlBuilder);
        }

        private ISqlWhereAndOr Where(string left, SqlOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            var builder = (SqlBuilderBase)sqlBuilder;

            _clause.AddParameters(builder.Statement.Parameters);

            return this.AddWhere(
                new WhereElement(left, sqlOperator, builder.Statement, SqlRightType.Statement));
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause by an ISqlWhereCondition object.
        /// </summary>
        /// <param name="condition">
        ///     An ISqlWhereCondition which represents a search condition in the WHERE clause. 
        ///     This object can be created by using the SqlBuilder.Where method and so on.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(ISqlWhereCondition condition)
        {
            return this.Where(condition, "");
        }

        /// <summary>
        ///     Creates a WHERE clause, adds a search condition to the WHERE clause by an ISqlWhereCondition object, 
        ///     and specifies a name for the search condition.
        /// </summary>
        /// <param name="condition">
        ///     An ISqlWhereCondition which represents a search condition in the WHERE clause. 
        ///     This object can be created by using the SqlBuilder.Where method and so on.
        /// </param>
        /// <param name="whereName">
        ///     The name for the search condition. It can be used to remove a search condition from the WHERE clause.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr Where(ISqlWhereCondition condition, string whereName)
        {
            var hc = (SqlWhereCondition)condition;

            if (hc.Clause.TryGet(out WhereElement where))
            {
                _clause.AddParameters(hc.BuilderParameters);

                if (String.IsNullOrWhiteSpace(where.Condition.ElementName))
                {
                    where.Condition.ElementName = whereName;
                }

                where.ElementName = whereName;

                return this.AddWhere(where);
            }
            else
            {
                throw new ArgumentException("Invalid 'Where' condition.");
            }
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a raw SQL to the WHERE clause.
        /// </summary>
        /// <param name="whereClause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereRaw(string whereClause, params ISqlParameter[] parameters)
        {
            return this.WhereRaw(whereClause, "", parameters);
        }

        /// <summary>
        ///     Creates a WHERE clause, adds a raw SQL to the WHERE clause, and specifies a name for the raw SQL.
        /// </summary>
        /// <param name="whereClause">A string of raw SQL to be added to the WHERE clause.</param>
        /// <param name="whereName">The name for the raw SQL. It can be used to remove the raw SQL from the WHERE clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which defines the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereRaw(string whereClause, string whereName, params ISqlParameter[] parameters)
        {
            var condition = new WhereElement(
                null, SqlOperator.Equals, whereClause, SqlRightType.Clause, whereName);

            _clause.AddParameters(parameters);

            return this.AddWhere(condition);
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL 
        ///     expression on the left of the operator and a specific value on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereValue(string left, object value)
        {
            return this.AddWhere(
                new WhereElement(left, SqlOperator.Equals, value, SqlRightType.Value));
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Specifies a SQL expression on 
        ///     the left of the operator and a specific value on the right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">A specific value on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            return this.AddWhere(
                new WhereElement(left, SqlOperatorConverter.ToSqlOperator(sqlOperator), value, SqlRightType.Value));
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Uses IS NULL operator to search for values that are null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereIsNull(string left)
        {
            return this.Where(left, SqlBinaryOperator.Is, "null");
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. 
        ///     Uses IS NOT NULL operator to search for values that are not null.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the IS NOT NULL operator.</param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereIsNotNull(string left)
        {
            return this.Where(left, SqlBinaryOperator.IsNot, "null");
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses EXISTS to test for the 
        ///     existence of the record returned from a subquery.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the EXISTS operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereExists(ISqlBuilder sqlBuilder)
        {
            return this.Where("", SqlOperator.Exists, sqlBuilder);
        }

        /// <summary>
        ///     Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses the NOT EXISTS 
        ///     operator to test if the record returned from a subquery does not exist.
        /// </summary>
        /// <param name="sqlBuilder">
        ///     An ISqlBuilder object which represents a SQL subquery on the right of the EXISTS operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlWhereAndOr object which can be used to add more search conditions to the current WHERE clause.
        /// </returns>
        public ISqlWhereAndOr WhereNotExists(ISqlBuilder sqlBuilder)
        {
            return this.Where("", SqlOperator.NotExists, sqlBuilder);
        }

        #region GroupBy
        /// <summary>
        ///     Creates a GROUP BY clause and adds an array of SQL expressions (each expression specifies
        ///     a column or a non-aggregate calculation on a column).
        /// </summary>
        /// <param name="groupExpressions">
        ///     An array of SQL expressions (each expression specifies a column or a non-aggregate calculation on a column).
        /// </param>
        /// <returns>
        ///     Returns the current ISqlGroupBuilder object.
        /// </returns>
        public ISqlGroupBuilder GroupBy(params string[] groupExpressions)
        {
            return _group.GroupBy(groupExpressions);
        }

        /// <summary>
        ///     Creates a GROUP BY clause, and adds a raw SQL string containing one or more column expressions (each expression
        ///     specifies a column or a non-aggregate calculation on a column).
        /// </summary>
        /// <param name="groupClause">
        ///     A raw SQL string containing one or more column expressions (each expression specifies a column or a 
        ///     non-aggregate calculation on a column).
        /// </param>
        /// <returns>
        ///     Returns the current ISqlGroupBuilder object.
        /// </returns>
        public ISqlGroupBuilder GroupBy(string groupClause)
        {
            return _group.GroupBy(groupClause);
        }

        /// <summary>
        ///     Returns an ISqlGroupBuilder object which represents an object used to build the GROUP BY clause in a SQL statement.
        /// </summary>
        /// <returns>
        ///     Returns the ISqlGroupBuilder object which represents an object used to build the GROUP BY clause in a SQL statement.
        /// </returns>
        public ISqlGroupBuilder AsGroupBy()
        {
            return (SqlGroupBuilder)_group;
        }
        #endregion

        #region Having
        /// <summary>
        ///     Creates a HAVING clause, and adds a search condition to the HAVING clause. You can specify 
        ///     a SQL expression on the left the operator and a specific value on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="value">
        ///     The specific value on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr HavingValue(string left, object value)
        {
            return _group.HavingValue(left, value);
        }

        /// <summary>
        ///     Creates a HAVING clause, and adds a search condition to the HAVING clause. You can specify the SQL 
        ///     expression on the left the operator and a specific value on the right of the operator.
        /// </summary>
        /// <param name="left">An expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator which is the operator to test the expression on the left and the value on the right.
        /// </param>
        /// <param name="value">
        ///     The specific value on the right of the operator.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr HavingValue(string left, SqlBinaryOperator sqlOperator, object value)
        {
            return _group.HavingValue(left, sqlOperator, value);
        }

        /// <summary>
        ///     Creates a HAVING clause, and adds a search condition to the HAVING clause. You can specify a SQL 
        ///     expression on the left of the operator and a SQL parameter on the right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter on the right of the operator. 
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(string left, ISqlParameter parameter)
        {
            return _group.Having(left, parameter);
        }

        /// <summary>
        ///     Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify a SQL expression 
        ///     on the left of the operator and a SQL parameter on the right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="parameter">
        ///     An ISqlParameter object which represents a SQL parameter on the right of the operator. 
        ///     It can be created by calling the SqlBuilder.Parameter method.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(string left, SqlBinaryOperator sqlOperator, ISqlParameter parameter)
        {
            return _group.Having(left, sqlOperator, parameter);
        }

        /// <summary>
        ///     Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify SQL expressions
        ///     on both the left and right of the operator. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(string left, string right)
        {
            return _group.Having(left, right);
        }

        /// <summary>
        ///     Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify SQL expressions 
        ///     on both the left and right of the operator.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator, which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="right">A SQL expression on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(string left, SqlBinaryOperator sqlOperator, string right)
        {
            return _group.Having(left, sqlOperator, right);
        }

        /// <summary>
        ///     Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify a SQL expression on the 
        ///     left of the operator and a SQL subquery on the right side. The operator is '='.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(string left, ISqlBuilder sqlBuilder)
        {
            return _group.Having(left, sqlBuilder);
        }

        /// <summary>
        ///     Creates a HAVING clause and adds a search condition to the HAVING clause. You can specify a SQL expression on the 
        ///     left of the operator and a SQL subquery on the right side.
        /// </summary>
        /// <param name="left">A SQL expression on the left of the operator.</param>
        /// <param name="sqlOperator">
        ///     An enumeration value of SqlBinaryOperator which is the operator to test the two expressions on the left and right.
        /// </param>
        /// <param name="sqlBuilder">An ISqlBuilder object which represents a SQL subquery on the right of the operator.</param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(string left, SqlBinaryOperator sqlOperator, ISqlBuilder sqlBuilder)
        {
            return _group.Having(left, sqlOperator, sqlBuilder);
        }

        /// <summary>
        ///     Creates a HAVING clause, and adds a search condition to the HAVING clause by an ISqlHavingCondition object.
        /// </summary>
        /// <param name="condition">
        ///     An ISqlHavingCondition object which represents a search condition in the HAVING clause.
        ///     This object can be created by using the SqlBuilder.Having method and so on.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(ISqlHavingCondition condition)
        {
            return _group.Having(condition);
        }

        /// <summary>
        ///     Creates a HAVING clause and adds a raw SQL to the HAVING clause.
        /// </summary>
        /// <param name="havingClause">A string of raw SQL to be added to the HAVING clause.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which define the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr HavingRaw(string havingClause, params ISqlParameter[] parameters)
        {
            return _group.HavingRaw(havingClause, parameters);
        }

        /// <summary>
        ///     Creates a HAVING clause, and adds a search condition to the HAVING clause by an ISqlHavingCondition 
        ///     object and specifies the name for the search condition.
        /// </summary>
        /// <param name="condition">
        ///     An ISqlHavingCondition object which represents a search condition in the HAVING clause.
        ///     This object can be created by using the SqlBuilder.Having method and so on.
        /// </param>
        /// <param name="alias">The name for the search condition.</param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr Having(ISqlHavingCondition condition, string alias)
        {
            return _group.Having(condition, alias);
        }

        /// <summary>
        ///     Creates a HAVING clause, and adds a raw SQL to the HAVING clause and specifies a name for the raw SQL.
        /// </summary>
        /// <param name="havingClause">A string of raw SQL to be added to the HAVING clause.</param>
        /// <param name="alias">The name for the raw SQL.</param>
        /// <param name="parameters">
        ///     (Optional) An array of ISqlParameter objects which define the SQL parameters used in the raw SQL.
        /// </param>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object which can be used to add more search conditions to the current HAVING clause.
        /// </returns>
        public ISqlHavingAndOr HavingRaw(string havingClause, string alias, params ISqlParameter[] parameters)
        {
            return _group.HavingRaw(havingClause, alias, parameters);
        }

        /// <summary>
        ///     Returns an ISqlHavingAndOr object that can be used to add a search condition to a group or an aggregate in the HAVING clause.
        /// </summary>
        /// <returns>
        ///     Returns the ISqlHavingAndOr object that can add a search condition to a group or an aggregate in the HAVING clause.
        /// </returns>
        public ISqlHavingAndOr AsHaving()
        {
            return _group.AsHaving();
        }
        #endregion

        #region OrderBy
        /// <summary>
        ///     Creates an ORDER BY clause and specifies one or more sort criteria in ascending order.
        /// </summary>
        /// <param name="orderExpressions">
        ///     (Optional) An array of expressions which specify a column or an expression on which to sort the query result set.
        /// </param>
        /// <returns>
        ///     Returns an ISqlOrderThenBuilder object which can be used to add more sort criteria to the current ORDER BY clause.
        /// </returns>
        public ISqlOrderThenBuilder OrderByAscending(params string[] orderExpressions)
        {
            return _group.OrderByAscending(orderExpressions);
        }

        /// <summary>
        ///     Creates an ORDER BY clause and specifies one or more sort criteria in descending order.
        /// </summary>
        /// <param name="orderExpressions">
        ///     (Optional) An array of expressions which specify a column or an expression on which to sort the query result set.
        /// </param>
        /// <returns>
        ///     Returns an ISqlOrderThenBuilder object which can be used to add more sort criteria to the current ORDER BY clause.
        /// </returns>
        public ISqlOrderThenBuilder OrderByDescending(params string[] orderExpressions)
        {
            return _group.OrderByDescending(orderExpressions);
        }

        /// <summary>
        ///     Creates an ORDER BY clause and specifies one or more sort criteria using a raw SQL.
        /// </summary>
        /// <param name="orderClause">A string of raw SQL which specifies one or more sort criteria.</param>
        /// <returns>
        ///     Returns an ISqlOrderThenBuilder object which can be used to add more sort criteria to the current ORDER BY clause.
        /// </returns>
        public ISqlOrderThenBuilder OrderByRaw(string orderClause)
        {
            return _group.OrderByRaw(orderClause);
        }

        /// <summary>
        ///     Returns an ISqlOrderThenBuilder object which can be used to add sorting criteria to the ORDER clause.
        /// </summary>
        /// <returns>
        ///     Returns an ISqlOrderThenBuilder object which can be used to add sorting criteria to the ORDER clause.
        /// </returns>
        public ISqlOrderThenBuilder AsOrderBy()
        {
            return _group.AsOrderBy();
        }

        /// <summary>
        ///     Creates an ORDER BY clause; and specifies one or more sort criteria using a raw SQL and specifies an alias for the raw SQL.
        /// </summary>
        /// <param name="orderClause">A string of raw SQL which specifies one or more sort criteria.</param>
        /// <param name="alias">The alias for the raw SQL.</param>
        /// <returns>
        ///     Returns an ISqlOrderThenBuilder object which can be used to add more sort criteria to the current ORDER BY clause.
        /// </returns>
        public ISqlOrderThenBuilder OrderByRaw(string orderClause, string alias)
        {
            return _group.OrderByRaw(orderClause, alias);
        }
        #endregion

        /// <summary>
        ///     Removes all of the search conditions that have been added to the WHERE clause.
        /// </summary>
        /// <returns>
        ///     Returns true if the removal is successful, and returns false if there were no search conditions in the WHERE clause.
        /// </returns>
        public bool RemoveWhere()
        {
            if (_clause.TryGet<WhereElement>(out var whereElement))
            {
                var isSuccess = _clause.Remove(whereElement);

                if (isSuccess && _builder is SqlBuilderBase builderBase)
                {
                    builderBase.Statement.RemoveAllParameters();
                }

                return isSuccess;
            }

            return false;
        }

        /// <summary>
        ///     Removes the search condition (specified by name) from the WHERE clause.
        /// </summary>
        /// <param name="whereName">The name for one or more search conditions in the WHERE clause.</param>
        /// <returns>
        ///     Returns true if the removal is successful, and returns false if the specified search
        ///     condition name does not exist in the WHERE clause.
        /// </returns>
        public bool RemoveWhere(string whereName)
        {
            if (_clause.TryGet<WhereElement>(out var whereElement))
            {
                if (whereElement.ElementName.Equals(whereName, StringComparison.OrdinalIgnoreCase)
                    && whereElement.Condition.AndOr.Count == 0)
                {
                    return _clause.Remove(whereElement);
                }
                else
                {
                    if (this.ReBuildWhereCondition(whereElement.Condition, whereName, out var newCondition))
                    {
                        if (newCondition == null)
                        {
                            return _clause.Remove(whereElement);
                        }
                        else
                        {
                            whereElement.Condition = newCondition;

                            return true;
                        }
                    }
                    else
                    {
                        return false;
                    }
                }
            }

            return false;
        }

        private bool ReBuildWhereCondition(ConditionElement condition, string whereName,
            out ConditionElement newCondition)
        {
            var isFoundCondition = false;

            newCondition = null;

            if (!condition.ElementName.Equals(whereName, StringComparison.OrdinalIgnoreCase))
            {
                newCondition = new ConditionElement(condition.Left, condition.Operator,
                                   condition.Right, condition.RightType, condition.IsUnaided)
                {
                    ElementName = condition.ElementName
                };
            }
            else
            {
                isFoundCondition = true;
            }

            foreach (var item in condition.AndOr)
            {
                var sqlStitchingType = item.Value;
                var key = (ConditionElement)item.Key;

                if (key.ElementName.Equals(whereName, StringComparison.OrdinalIgnoreCase))
                {
                    isFoundCondition = true;
                }

                if (key.ElementName.Equals(whereName, StringComparison.OrdinalIgnoreCase)
                    && key.AndOr.Count == 0)
                {
                    continue;
                }
                else
                {
                    ConditionElement tempCondition = null;

                    if (key.AndOr.Count > 0)
                    {
                        if (isFoundCondition)
                        {
                            this.ReBuildWhereCondition(key, whereName, out tempCondition);
                        }
                        else
                        {
                            isFoundCondition = this.ReBuildWhereCondition(key, whereName, out tempCondition);
                        }

                    }
                    else
                    {
                        if (!key.ElementName.Equals(whereName, StringComparison.OrdinalIgnoreCase))
                        {
                            tempCondition = new ConditionElement(key.Left, key.Operator,
                                            key.Right, key.RightType, key.IsUnaided)
                            {
                                ElementName = key.ElementName
                            };
                        }
                    }

                    if (newCondition == null)
                    {
                        newCondition = tempCondition;
                    }
                    else
                    {
                        if (tempCondition != null)
                        {
                            newCondition?.AndOr.Add(tempCondition, sqlStitchingType);
                        }
                    }
                }
            }

            return isFoundCondition;
        }
    }
}
